<?php
class xl_posts{
    function read($id){
        global $dbh;
        $sql='SELECT * FROM posts WHERE id=:id LIMIT 0,1';
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':id'=>$id));
        return  $sth->fetch(PDO::FETCH_ASSOC);
    }
    function updatde_status($id){
        global $dbh;
        $sql='UPDATE posts SET status=1-status WHERE id=:id';
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':id'=>$id));
        return $sth;
        
    }
    function updatde_highlights($id){
        global $dbh;
        $sql='UPDATE posts SET highlights=1-highlights WHERE id=:id';
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':id'=>$id));
        return $sth;
        
    }
    function read_list(){
        global $dbh;
        //$sql='SELECT pos.*, (SELECT cate.category_name FROM categories cate WHERE  pos.category_id=cate.id) category_name FROM posts pos ';
        $sql='select posts.*,categories.type_category,categories.category_name from categories,posts where posts.category_id=categories.id ORDER BY posts.id DESC';
        $sth=$dbh->prepare($sql);
        $sth->execute();
        return $sth->fetchAll();
    }
    
    function read_category(){
        global $dbh;
        $sql='SELECT * FROM categories';
        $sth=$dbh->prepare($sql);
        $sth->execute();
        return $sth->fetchAll();
    }
   
    function insert($data){
        global $dbh;
        $sql="INSERT INTO posts(category_id,post_name,alias,title,keyword,description,image,highlights,status,summary,content,day_time) 
            VALUE (:category_id,:post_name,:alias,:title,:keyword,:description,:image,:highlights,:status,:summary,:content,:day_time)";
        $sth=$dbh->prepare($sql);
        $sth->execute($data);
        return $sth;
    }
    function update($data){
        global $dbh;
        if($data['image']!=''){
            $sql='UPDATE posts SET category_id=:category_id,post_name=:post_name,title=:title,keyword=:keyword,description=:description,image=:image,highlights=:highlights,status=:status,summary=:summary,content=:content,day_time=:day_time WHERE id=:id';
        } else {
            $sql='UPDATE posts SET category_id=:category_id,post_name=:post_name,title=:title,keyword=:keyword,description=:description,highlights=:highlights,status=:status,summary=:summary,content=:content,day_time=:day_time WHERE id=:id';
            unset($data['image']);
        }
        $sth=$dbh->prepare($sql);
        $sth->execute($data);
        return $sth;
    }
    function delete($id){
        global $dbh;
        $sql='DELETE FROM posts WHERE id=:id';
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':id'=>$id));
        return $sth;
    }
    
    function delete_all($id){
        global $dbh;
        $sql='SELECT * FROM posts WHERE id=:id';
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':id'=>$id));
        return $sth->fetch(PDO::FETCH_ASSOC);
    }
    function test($id,$post_name){
        global $dbh;
        $sql='SELECT * FROM posts WHERE id!=:id AND post_name=:post_name';
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':id'=>$id,':post_name'=>$post_name));
        return $sth->fetchAll(PDO::FETCH_ASSOC);
    }
    function test_name($alias){
        global $dbh;
        $sql='SELECT * FROM posts WHERE alias=:alias';
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':alias'=>$alias));
        return $sth->fetch(PDO::FETCH_ASSOC);
    }
}
